package com.dsp.services.impl;

import java.math.BigDecimal;


import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Service;

import com.core.exception.StandardException;
import com.core.util.DspConnection;
import com.core.util.DspUtil;
import com.core.util.GenericServiceHibernate;
import com.dsp.bean.Calc;
import com.dsp.bean.Company;
import com.dsp.bean.CompanyAccount;
import com.dsp.bean.Employee;
import com.dsp.bean.InsureResult;
import com.dsp.bean.InsureResultsSub;
import com.dsp.bean.JobsMessage;
import com.dsp.bean.PersonalAccount;
import com.dsp.bean.Seq;
import com.dsp.services.CalcService;
import com.dsp.services.CompanyAccountService;
import com.dsp.services.CompanyService;
import com.dsp.services.EmployeeService;
import com.dsp.services.InsureResultService;
import com.dsp.services.InsureResultsSubService;
import com.dsp.services.PersonalAccountService;
import com.dsp.services.SeqService;

@Service("calcService")
public class CalcServiceImpl extends GenericServiceHibernate<Calc, Long> implements CalcService {

	private Long jobId;

	/**
	 * 员工
	 */
	private EmployeeService employeeService = null;
	/**
	 * 公司
	 */
	private CompanyService companyService = null;
	/**
	 * 社保个人
	 */
	private PersonalAccountService personalAccountService = null;
	/**
	 * 社保公司
	 */
	private CompanyAccountService companyAccountService = null;

	private SeqService seqService = null;

	/**
	 * 执行社保计算作业
	 * 
	 * @throws Exception
	 */
	@Override
	public Integer calc(Long jobId, Calc c, List<JobsMessage> error) throws Exception {
		this.jobId = jobId;
		List<Employee> list = this.getEmployee(c);
		List<Company> company = this.getCompany(c);
		if (DspUtil.isEmptyList(list)) {
			error.add(addMessage("该范围下没有可计算的员工"));
			return 1;
		}

		Map<String, List<PersonalAccount>> mpa = this.getPersonalAccount(company);
		Map<String, List<CompanyAccount>> mca = this.getCompanyAccount(company);
		List<InsureResult> result = new ArrayList<InsureResult>();
		List<InsureResultsSub> resultSub = new ArrayList<InsureResultsSub>();
		Seq seq = this.getId();
		long RQ = seq.getSeqValue().longValue();
		InsureResult ir = null;
		// 公司信息
		for (Employee emp : list) {
			boolean pBoolean = true, cBoolean = true;
			Company co = this.getCompany(emp.getCompany(), company);
			List<PersonalAccount> lpa = mpa.get(co.getProvince() + ";" + co.getCity() + ";" + co.getArea() + ";" + emp.getHukou());
			if (DspUtil.isEmptyList(lpa)) {
				error.add(addMessage(co.getCname() + "没有对应的社保个人明细信息"));
			}
			List<CompanyAccount> lca = mca.get(co.getProvince() + ";" + co.getCity() + ";" + co.getArea() + ";" + emp.getHukou());
			if (DspUtil.isEmptyList(lca)) {
				error.add(addMessage(co.getCname() + "没有对应的社保公司明细信息"));
			}
			ir = new InsureResult();
			ir.setId(RQ++);
			ir.setCompany(emp.getCompany());
			ir.setEmpId(emp.getId());
			ir.setEmpName(emp.getEmpname());
			ir.setHukou(emp.getHukou().longValue());
			ir.setYearMonth(c.getCalcYearMonth());
			pBoolean = calcPersonalInsure(error, resultSub, ir, emp, pBoolean, lpa);
			cBoolean = calcCompanyInusre(error, resultSub, ir, emp, cBoolean, lca);
			if (pBoolean || cBoolean) {
				result.add(ir);
			}
		}
		
		this.save(c , result, resultSub , error);
		seq.setSeqValue(Integer.valueOf(RQ + ""));
		this.update(seq);
		return error.size() > 0 ? 1 : 2;
	}

	/**
	 * 计算员工公司社保信息
	 * @param error
	 * @param resultSub
	 * @param ir
	 * @param emp
	 * @param cBoolean
	 * @param lca
	 * @return
	 */
	private boolean calcCompanyInusre(List<JobsMessage> error, List<InsureResultsSub> resultSub, InsureResult ir, Employee emp,
			boolean cBoolean, List<CompanyAccount> lca) {
		InsureResultsSub irs;
		if (lca == null) {
			error.add(addMessage("员工:" + emp.getEmpname() + ",没有设置公司社保"));
			cBoolean = false;
		} else {
			boolean hasError = true;
			for (CompanyAccount ca : lca) {
				if (emp.getIsInsure() == 0 && ca.getItemId() > 0) { //如果没有加社保
					if(hasError){
						error.add(addMessage("没有给员工:" + emp.getEmpname() + "缴社保."));
						hasError = !hasError;
					}
					continue;//没有加社保
				}
				if (emp.getIsProvident() == 0 && ca.getItemId() == 0) {
					error.add(addMessage("没有给员工:" + emp.getEmpname() + "缴公积金."));
					continue; //没有加公积金
				}
				BigDecimal bd = null;
				bd = ca.getItemId() == 0 ? emp.getProvidentBase() : emp.getInsureBase();
				if(bd == null) continue; // 如果没有设置基数，则代表不需要计算
				double value = 0;
				if (ca.getCompanyType() == 1) {
					value = bd.doubleValue() * ca.getRate().doubleValue() / 100;
				} else {
					value = ca.getFixValue().doubleValue();
				}
				value = compare(ca.getCompanyLower(), ca.getCompanyLimit(), value);
				irs = new InsureResultsSub();
				initInsureResultsSub(irs , ir.getId(), 2 , Long.valueOf(ca.getItemId()) , bd, BigDecimal.valueOf(value));
				resultSub.add(irs);
			}
		}
		return cBoolean;
	}

	/**
	 * 计算员工个人社保信息
	 * @param error
	 * @param resultSub
	 * @param ir
	 * @param emp
	 * @param pBoolean
	 * @param lpa
	 * @return
	 */
	private boolean calcPersonalInsure(List<JobsMessage> error, List<InsureResultsSub> resultSub, InsureResult ir, Employee emp,
			boolean pBoolean, List<PersonalAccount> lpa) {
		InsureResultsSub irs;
		if (lpa == null) {
			error.add(addMessage("员工:" + emp.getEmpname() + ",没有设置个人社保"));
			pBoolean = false;
		} else {
			boolean hasError = true;
			for (PersonalAccount pa : lpa) {
				if (emp.getIsInsure() == 0 && pa.getItemId() > 0) { //如果没有加社保
					if(hasError){
						error.add(addMessage("没有给员工:" + emp.getEmpname() + "缴社保."));
						hasError = !hasError;
					}
					continue;//没有加社保
				}
				if (emp.getIsProvident() == 0 && pa.getItemId() == 0) {
					error.add(addMessage("没有给员工:" + emp.getEmpname() + "缴公积金."));
					continue; //没有加公积金
				}
				double value = 0;
				BigDecimal bd = null;
				bd = pa.getItemId() == 0 ? emp.getProvidentBase() : emp.getInsureBase();
				if (bd == null) continue;
				if (pa.getPersonalType() == 1) { // 百分比
					value = bd.doubleValue() * pa.getRate().doubleValue() / 100 ;
				} else { // 定额
					value = pa.getFixValue().doubleValue();
				}
				value = compare(pa.getPersonalLower(), pa.getPersonalLimit(), value);
				irs = new InsureResultsSub();
				initInsureResultsSub(irs , ir.getId(), 1, Long.valueOf(pa.getItemId()) , bd, BigDecimal.valueOf(value));
				resultSub.add(irs);
			}
		}
		return pBoolean;
	}
	/**
	 * 初始化社保明细 
	 * @param irs
	 * @param mainId
	 * @param insureType
	 * @param itemId
	 * @param base
	 * @param money
	 */
	private void initInsureResultsSub(InsureResultsSub irs , Long mainId,Integer insureType , Long itemId, BigDecimal base , BigDecimal money){
		irs.setMainId(mainId);
		irs.setInsureType(insureType);
		irs.setInsureItem(itemId);
		irs.setInsureBase(base);
		irs.setInsureMoney(money);
	}

	/**
	 * 保存数据
	 * 
	 * @param list
	 * @param subList
	 * @throws StandardException
	 */
	@Deprecated
	public void save0(List<InsureResult> list, List<InsureResultsSub> subList) throws Exception {
		InsureResultService resultService = (InsureResultService) DspUtil.getBean("insureResultService");
		InsureResultsSubService resultsSubService = (InsureResultsSubService) DspUtil.getBean("insureResultsSubService");
		DetachedCriteria dc = null;
		List<InsureResult> tmpList = null;
		for (InsureResult ir : list) {
			dc = DetachedCriteria.forClass(InsureResult.class);
			dc.add(Restrictions.eq("yearMonth", ir.getYearMonth()));
			dc.add(Restrictions.eq("empId", ir.getEmpId()));
			dc.add(Restrictions.eq("company", ir.getCompany()));
			dc.add(Restrictions.eq("hukou", ir.getHukou()));
			tmpList = resultService.likeBy(dc);
			if (DspUtil.isEmptyList(tmpList) == false){
				InsureResult __iR = tmpList.get(0);
				resultService.delete(__iR);
				DspConnection.delete(getInsureSub(__iR.getId()));
			}
			resultService.save(ir);
		}
		
		for (InsureResultsSub irs : subList) {
			resultsSubService.save(irs);
		}
	}
	/**
	 * 先删除再新增
	 * @param c 计算条件
	 * @param list 主表数据
	 * @param subList 子表数据
	 * @throws SQLException 
	 */
	private void save(Calc c , List<InsureResult> list, List<InsureResultsSub> subList, List<JobsMessage> error) {
		String condition  = " where insure_yearmonth = '"+ c.getCalcYearMonth() +"' and company in ("+ DspUtil.getSQLIn(c.getCompany()) +")";
		String deleteMain = "delete from insure_results " + condition;
		String deleteSub  = "delete from insure_results_sub where main_id in (select id from insure_results " + condition + ")";
		String insertMain = "insert into insure_results(id,insure_yearmonth,emp_id,emp_name,company,hukou) values(?,?,?,?,?,?)";
		String insertSub  = "insert into insure_results_sub(main_id,insure_type,insure_base,insure_money,insure_item)values(?,?,?,?,?)";
		Connection connection = null;
		java.sql.PreparedStatement pstmt=null;
		try {
			connection = DspConnection.getConnection();
			connection.setAutoCommit(false);
			pstmt = connection.prepareStatement(deleteSub);
			pstmt.addBatch();
			pstmt.executeBatch();
			pstmt = connection.prepareStatement(deleteMain);
			pstmt.addBatch();
			pstmt.executeBatch();
			pstmt = connection.prepareStatement(insertMain);
			int i = 1;
			for(InsureResult ir : list){
				i = 1;
				pstmt.setLong(i++, ir.getId());
				pstmt.setString(i++	, ir.getYearMonth());
				pstmt.setLong(i++, ir.getEmpId());
				pstmt.setString(i++, ir.getEmpName());
				pstmt.setLong(i++, ir.getCompany());
				pstmt.setLong(i++, ir.getHukou());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			pstmt = connection.prepareStatement(insertSub);
			for(InsureResultsSub irs : subList){
				i = 1;
				pstmt.setLong(i++, irs.getMainId());
				pstmt.setInt(i++, irs.getInsureType());
				pstmt.setBigDecimal(i++, irs.getInsureBase());
				pstmt.setBigDecimal(i++, irs.getInsureMoney());
				pstmt.setLong(i++, irs.getInsureItem());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			connection.commit();
			connection.setAutoCommit(true);
		} catch (SQLException e) {
			try {
				connection.rollback();
				error.add(addMessage("计算写入值时出现异常"));
				error.add(addMessage(e.getLocalizedMessage()));
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			DspConnection.closeConnection(pstmt, connection);
		}
	}

	/**
	 * 比较
	 * 
	 * @param lower
	 * @param limit
	 * @param value
	 * @return
	 */
	private double compare(BigDecimal lower, BigDecimal limit, double value) {
		// 下限
		value = value < lower.doubleValue() ? lower.doubleValue() : value;
		// 上限
		value = value > limit.doubleValue() ? limit.doubleValue() : value;
		return value;
	}

	/**
	 * 主键策略
	 * 
	 * @return
	 */
	private Seq getId() {
		this.seqService = (SeqService) DspUtil.getBean("seqService");
		return this.seqService.findById(Long.valueOf("1"));
	}

	/**
	 * 更新流水号
	 * 
	 * @param seq
	 */
	private void update(Seq seq) throws Exception {
		this.seqService.update(seq);
	}

	/**
	 * 通过key查找对应的company信息
	 * 
	 * @param key
	 * @param listCompany
	 * @return
	 */
	private Company getCompany(Long key, List<Company> listCompany) {
		for (Company c : listCompany) {
			if (c.getId().longValue() == key.longValue()) {
				return c;
			}
		}
		return null;
	}

	/**
	 * 查找可计算范围的员工社保
	 * @param calc
	 * @return
	 */
	private List<Employee> getEmployee(Calc calc) {
		DetachedCriteria dc = DetachedCriteria.forClass(Employee.class);
		if (calc.getCompany() != null && calc.getCompany().size() > 0) {
			dc.add(Restrictions.in("company", calc.getCompany()));
		}
		dc.add(Restrictions.or(Restrictions.gt("isInsure", 0), Restrictions.gt("isProvident", 0))); //排除未加保的员工
		Date[] calcDate = this.getDate(calc.getCalcYearMonth());
		dc.add(Restrictions.le("bargainBegin", calcDate[1]));
		dc.add(Restrictions.ge("bargainEnd", calcDate[0]));
		this.employeeService = (EmployeeService) DspUtil.getBean("employeeService");
		return this.employeeService.likeBy(dc);
	}

	/**
	 * 初始化公司信息
	 * 
	 * @param calc
	 * @return
	 */
	private List<Company> getCompany(Calc calc) {
		DetachedCriteria dc = DetachedCriteria.forClass(Company.class);
		if (calc.getCompany() != null && calc.getCompany().size() > 0) {
			dc.add(Restrictions.in("id", calc.getCompany()));
		}
		this.companyService = (CompanyService) DspUtil.getBean("companyService");
		return this.companyService.likeBy(dc);
	}

	/**
	 * 初始化社保个人信息
	 * 
	 * @return
	 */
	private Map<String, List<PersonalAccount>> getPersonalAccount(List<Company> companyList) {
		DetachedCriteria dc = DetachedCriteria.forClass(PersonalAccount.class);
		Map<String, List<PersonalAccount>> map = new HashMap<String, List<PersonalAccount>>();
		getCondition(companyList, dc);
		this.personalAccountService = (PersonalAccountService) DspUtil.getBean("personalAccountService");
		List<PersonalAccount> personalList = this.personalAccountService.likeBy(dc);
		List<PersonalAccount> tmpList = null;
		String key = null;
		for (PersonalAccount personal : personalList) {
			key = personal.getProvince() + ";" + personal.getCity() + ";" + personal.getArea() + ";" + personal.getHukou();
			if (map.get(key) != null) {
				tmpList = (List<PersonalAccount>) map.get(key);
			} else {
				tmpList = new ArrayList<PersonalAccount>();
				map.put(key, tmpList);
			}
			tmpList.add(personal);
		}
		return map;
	}

	/**
	 * 公司社保信息
	 * 
	 * @param companyList
	 * @return
	 */
	private Map<String, List<CompanyAccount>> getCompanyAccount(List<Company> companyList) {
		DetachedCriteria dc = DetachedCriteria.forClass(CompanyAccount.class);
		Map<String, List<CompanyAccount>> map = new HashMap<String, List<CompanyAccount>>();
		getCondition(companyList, dc);
		this.companyAccountService = (CompanyAccountService) DspUtil.getBean("companyAccountService");
		List<CompanyAccount> companyAccount = this.companyAccountService.likeBy(dc);
		List<CompanyAccount> tmpList = null;
		String key = null;
		for (CompanyAccount personal : companyAccount) {
			key = personal.getProvince() + ";" + personal.getCity() + ";" + personal.getArea() + ";" + personal.getHukou();
			if (map.get(key) != null) {
				tmpList = (List<CompanyAccount>) map.get(key);
			} else {
				tmpList = new ArrayList<CompanyAccount>();
				map.put(key, tmpList);
			}
			tmpList.add(personal);
		}
		return map;
	}

	/**
	 * 得到查询条件
	 * 
	 * @param companyList
	 * @param dc
	 */
	private void getCondition(List<Company> companyList, DetachedCriteria dc) {
		List<String> province = new ArrayList<String>();
		List<String> city = new ArrayList<String>();
		List<String> area = new ArrayList<String>();
		for (Company company : companyList) {
			province.add(company.getProvince());
			city.add(company.getCity());
			area.add(company.getArea());
		}
		if (DspUtil.isEmptyList(province) == false)
			dc.add(Restrictions.in("province", province));
		if (DspUtil.isEmptyList(city) == false)
			dc.add(Restrictions.in("city", city));
		if (DspUtil.isEmptyList(area) == false)
			dc.add(Restrictions.in("area", area));
	}

	/**
	 * 得到计算年月的第一天和最后一天
	 * @param yearMonth
	 * @return
	 */
	private Date[] getDate(String yearMonth) {
		return DspUtil.getFirstAndLastByMonth(yearMonth);
	}

	/**
	 * 异常信息
	 * 
	 * @param message
	 * @return
	 */
	private JobsMessage addMessage(String message) {
		JobsMessage jm = new JobsMessage();
		jm.setParent(jobId);
		jm.setErrorInfo(message);
		return jm;
	}
	
	private String getInsureSub(Long fk){
		return "delete from insure_results_sub where main_id="+ fk;
	}
}
